import json
from collections import OrderedDict
from flask import Flask, render_template, request
import pandas as pd
import os
import flask_excel


app = Flask(__name__)
flask_excel.init_excel(app)

df = pd.read_excel(
    os.path.join(app.root_path, 'tips.xlsx')
).reset_index().rename(
    columns={'index': 'id'}
)
title_list = df.columns.tolist()

df_search = df.copy()  # 查询的df
df_select = pd.DataFrame()  # 选择的


def get_search_df(kw):
    global df_search
    mask = df[['sex', 'smoker', 'day', 'time']].applymap(lambda x: kw in x)
    df_search = df[mask.any(axis=1)]


@app.route('/')
def hello_world():
    return render_template(
        'index.html',
        title_list=title_list
    )


@app.route('/api/search/')
def search():
    keyword = request.args.get('kw')
    print(df.to_dict(orient="records"))
    if not keyword:
        return {
            'tableData': df.to_dict(orient="records")
        }
    else:
        get_search_df(keyword)
        return {
            'tableData': df_search.to_dict(orient="records")
        }


@app.route('/api/select/')
def select():
    global df_select
    id_list_str = request.args.get('id_list')
    id_list = json.loads(id_list_str)
    if id_list:
        df_select = df[df['id'].isin(id_list)]
    else:
        df_select = pd.DataFrame()

    return ''


@app.route('/export/<tb>')
def export(tb):

    if tb == 'search':
        return flask_excel.make_response_from_records(
            df_search.to_dict(orient="records", into=OrderedDict),
            file_name='search',
            file_type='xlsx',
            sheet_name='sheet1'
        )
    elif tb == 'select':
        return flask_excel.make_response_from_records(
                df_select.to_dict(orient="records", into=OrderedDict),
                file_name='select',
                file_type='xlsx',
                sheet_name='sheet1'
            )
    else:
        return ''


if __name__ == '__main__':
    app.run()
